package com.liangtian.mdm.dm.jsp.util;

import com.liangtian.mdm.base.dao.JdbcDao;
import com.liangtian.mdm.dm.config.model.AttrConfig;
import com.liangtian.mdm.dm.listener.CacheUtil;
import com.liangtian.mdm.dm.model.model.Model;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

@Service
public class ExcelExpUtil {

    @Autowired
    private JdbcDao jdbcDao;
    @Autowired
    private CacheUtil cacheUtil;

    private XSSFRow row1 = null;
    private XSSFRow row2 = null;

    private static String BASIC_VIEW = "基本试图";
    private static String[] JOIN_TYPE = {"listTree", "selectList", "popList", "checkBoxType", "radioType"};

    private static short COLOR[] =
            {HSSFColor.LIGHT_GREEN.index, HSSFColor.PALE_BLUE.index, HSSFColor.LIGHT_YELLOW.index,
                    HSSFColor.TAN.index, HSSFColor.CORNFLOWER_BLUE.index, HSSFColor.TEAL.index,
                    HSSFColor.YELLOW.index, HSSFColor.LIGHT_CORNFLOWER_BLUE.index};//如果是多视图,定义多视图表头的背景颜色

    private static XSSFWorkbook workbook = null;//= new XSSFWorkbook();

    public void exportExcelTemp(Model model, List<AttrConfig> configLst, Map<String, List<AttrConfig>> map, HttpServletResponse resp) {

        workbook = new XSSFWorkbook(); // 声明一个工作薄，开始将上一类别的数据写入到excel
        XSSFSheet sheet = workbook.createSheet(model.getModelName().replace("/", "-")); //声明一个工作薄的sheet页
        sheet.setTabColor(new XSSFColor(java.awt.Color.RED));
        sheet.setDefaultColumnWidth(40);

        try {
            //是否多试图
            int isMoreView = model.getIsMoreView();
            //用来存储所有的关联类型对应的'字典表'的值(并且当前字典表对应的记录多于10个)
            Map<String, List<String>> mapSelect = new HashMap<String, List<String>>();
            //根据当前主数据的属性列表,生成模板的属性列以及属性列的提醒信息,其中也包括下拉框的值以及关联类型的值占用单独的一个sheet展示值
            mapSelect = fillVal(configLst, sheet, isMoreView, mapSelect);
            //多视图的试图名称
            if (1 == isMoreView) {//如果是多视图,则模板的第一行为试图名称(横向合并单元格,并带背景颜色)
                isMoreViewFillVal(configLst, sheet);
            }
            //如果是主从表,并且从表有数据
            for (Map.Entry<String, List<AttrConfig>> entry : map.entrySet()) {
                workbook.setSheetName(0, model.getModelName().replace("/", "-") + "(主表)");
                String modelId = entry.getKey().split(",,,")[0];
                String modelName1 = entry.getKey().split(",,,")[1];
                List<AttrConfig> acList = getList(entry.getValue(), model.getModelName());
                XSSFSheet sheetSon = workbook.createSheet(modelName1.replace("/", "-") + "(" + modelId + "_从表)");
                mapSelect = fillVal(acList, sheetSon, 0, mapSelect);
            }
            //下面的for循环是为了展示关联类型的数据,关联的字典表的数据量大于10的字典表具体数据展示
            for (Map.Entry<String, List<String>> entry : mapSelect.entrySet()) {
                fillNewSheet(entry.getKey().split(",,,")[0] + "(字典)", entry.getValue());
            }

            OutputStream out;
            out = resp.getOutputStream();
            setResponseHeader(resp, model.getModelName());
            workbook.write(out);
            workbook.close();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //仅仅是主从表的数据,在生成从表的时候,第一列要统一展示为:(主表模型名称)序号,并且提示用户必填
    private List<AttrConfig> getList(List<AttrConfig> acList, String name) {
        AttrConfig ac = new AttrConfig();
        ac.setAttrName("关联主表列名[]");
        ac.setAttrType("PCITC");
        ac.setIsNull(0);
        acList.add(0, ac);
        return acList;
    }

    //当前主数据是多视图,则用来展示多试图的第一行表头信息(试图名称---横向合并单元格)
    private static void isMoreViewFillVal(List<AttrConfig> configLst, XSSFSheet sheet) {
        AttrConfig acf = new AttrConfig();
        int count = 0;//多视图的时候合并单元格用
        int bgCount = 1;//多视图的时候取背景颜色用
        XSSFRow row0 = null;
        row0 = sheet.createRow(0);//如果是多试图,则生成试图名对应的表头第一行
        for (int i = 0; i < configLst.size(); i++) {
            AttrConfig ac = configLst.get(i);

            if (null != ac.getGroupName() && !ac.getGroupName().equals(acf.getGroupName())) {
                fillMoreViewHeader(sheet, row0, bgCount, count, i, (null == acf.getGroupName() ? BASIC_VIEW : acf.getGroupName()));
                count = i;
                bgCount++;
            }
            if (i == configLst.size() - 1) {
                fillMoreViewHeader(sheet, row0, bgCount, count, i + 1, null == acf.getGroupName() ? BASIC_VIEW : acf.getGroupName());
                bgCount++;
            }
            acf = ac;
        }
    }

    //填充值
    private Map<String, List<String>> fillVal(List<AttrConfig> configLst, XSSFSheet sheet, int isMoreView, Map<String, List<String>> mapSelect) {
        sheet.setDefaultColumnWidth(40);
        // sheet.setTabColor(HSSFColor.RED.index);
        sheet.setTabColor(new XSSFColor(java.awt.Color.RED));
        row1 = sheet.createRow(isMoreView);//属性
        row2 = sheet.createRow(isMoreView + 1);//属性提示
        List<String> remindList = new ArrayList<String>();//提示信息的list
        for (int i = 0; i < configLst.size(); i++) {
            AttrConfig ac = configLst.get(i);
            String remindStr = "";
            if (Arrays.asList(JOIN_TYPE).contains(ac.getAttrType())) {
                String[] str = null;
                String sql = "select distinct " + ac.getDataSourceShowColumn() + " from T_" + ac.getDataSourceTable() + " where state = 4 and " + ac.getDataSourceShowColumn() + " is not null";
                String sqlCount = "select count(1) from T_" + ac.getDataSourceTable() + " where state = 4";
                int count = jdbcDao.executeSqlForCount(sqlCount);
                List<String> strList = jdbcDao.executeSqlSelectId(sql);
                if (count <= 10) {//如果关联属性的值多于10个,则展示到单独的sheet页中,不再展示为下拉框里的值
                    str = (String[]) strList.toArray(new String[strList.size()]);
                    fillSelectCellVal(sheet, str, isMoreView + 2, i);
                    remindStr = "点击小三角,";
                } else {
                    Model modelSel = cacheUtil.getModel(ac.getDataSourceTable());
                    if (!checkIsContains(modelSel.getModelName(), ac, mapSelect)) {
                        mapSelect.put(modelSel.getModelName(), strList);
                    }
                    remindStr = "从'" + modelSel.getModelName() + "(字典)'sheet页中选择";
                }
            } else if ("PCITC".equals(ac.getAttrType())) {
                remindStr += "请从第一个sheet页中选择一列写到方括号中";
            }
            remindList.add(remindValue(ac, remindStr));
            fillCell(row1, (short) 23, i, ac.getIsNull(), ac.getAttrName(), false, true);//行,行高,第几个cell,是否必填,值,是否提示信息,是否需要样式
        }
        for (int j = 0; j < remindList.size(); j++) {
            fillCell(row2, (short) 18, j, 1, remindList.get(j), true, true);//行,行高,第几个cell,是否必填,值,是否提示信息,是否需要样式
        }
        return mapSelect;
    }

    //判断map中是否存在
    private boolean checkIsContains(String name, AttrConfig ac, Map<String, List<String>> mapSelect) {
        boolean existSign = false;
        for (Map.Entry<String, List<String>> entry : mapSelect.entrySet()) {
            String key = entry.getKey();
            if (key.equals(name)) {
                existSign = true;
                break;
            }
        }
        return existSign;
    }

    //添加一个sheet页,并加载数据,这个仅仅是为了写入关联类型的数据,写入的sheet页中只有一列内容
    private static void fillNewSheet(String attrName, List<String> list) {
        XSSFSheet sheet = workbook.createSheet(attrName.replace("/", "-"));
        Row row = null;
//		row = sheet.createRow(0);
//		fillCell(row, (short)18, 0, 0, attrName, false);
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i);
            fillCell(row, (short) 14, 0, 1, list.get(i), false, false);
        }
    }

    //单元格添值---正常单元格,非下拉框,并且不需要合并单元格
    private static void fillCell(Row row, short heigth, int i, int isNull, String value, boolean b, boolean styleIsNeed) {
        Cell cell = row.createCell(i);
        row.setHeightInPoints(heigth);
        if (styleIsNeed) {
            cell.setCellStyle(setCellStyle(0, isNull, b));
        }
//		isNeedStyle(cell,0,isNull,b);
        cell.setCellValue(value);
    }

    //单元格填值---合并单元格
    @SuppressWarnings("deprecation")
    private static void fillMoreViewHeader(XSSFSheet sheet, Row row, int bgCount, int count, int sum, String value) {
        if (count < sum - 1) {
            CellRangeAddress cra = new CellRangeAddress(0, 0, count, sum - 1);
            sheet.addMergedRegion(cra);
        }
        Cell cell = row.createCell(count);
        cell.setCellStyle(setCellStyle(bgCount, 1, false));
//		isNeedStyle(cell,bgCount,1,false);
        cell.setCellValue(value);
    }

    //单元格样式---判断当前单元格是否需要添加样式
//	private static void isNeedStyle(Cell cell,int isMoreViewHead,int isNull,boolean remind) {//多视图,可为空,提示信息
//		if(0<isMoreViewHead||0==isNull||remind){
//			cell.setCellStyle(setCellStyle(isMoreViewHead,isNull,remind));
//		}
//	}

    //单元格填值---下拉框
    @SuppressWarnings("deprecation")
    private static void fillSelectCellVal(XSSFSheet sheet, String[] datas, int col, int row) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
//		String[] datas = new String[] { "维持", "恢复", "调整" };
        if (ArrayUtils.isEmpty(datas)) {//如果关联的'字典表'没值,默认给一个空值
            datas = new String[1];
            datas[0] = "";
        }
//		if(datas.length>0){//如果datas为空,默认给了一个""值
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(datas);
        CellRangeAddressList addressList = null;
        XSSFDataValidation validation = null;
        addressList = new CellRangeAddressList(col, col, row, row);//起始行号，终止行号， 起始列号，终止列号
        validation = (XSSFDataValidation) dvHelper.createValidation(
                dvConstraint, addressList);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
//		}
    }

    //设置提示信息
    private static String remindValue(AttrConfig ac, String remindStr) {
        String str = "";
        if (0 == ac.getIsNull()) {//属性必填  ---    0:必填   1:非必填
            str += "必填,";
        } else {
            str += "非必填,";
        }
        if ("DATE".equals(ac.getAttrType())) {
            str += "日期格式：1987-06-24,";
//		}else if("selectList".equals(ac.getAttrType())||"listTree".equals(ac.getAttrType())||"popList".equals(ac.getAttrType())){
        } else if (Arrays.asList(JOIN_TYPE).contains(ac.getAttrType()) || "PCITC".equals(ac.getAttrType())) {
            str += remindStr;
        } else if ("NUMBER".equals(ac.getAttrType())) {
            str += "长度不超过" + ac.getAttrLength() + "位的整数";
        } else if ("NUMBERM".equals(ac.getAttrType())) {
//			String[] length = ac.getAttrLength().split(",");
            str += "小数,";
        } else if ("BLOB".equals(ac.getAttrType())) {
            str += "文件域";
        } else if ("CLOB".equals(ac.getAttrType())) {
            str += "文本";
        } else {
            str += "长度不超过" + ac.getAttrLength() + "字符(" + Integer.parseInt(ac.getAttrLength())/3 + "个汉字),";
        }

        return str;
    }


    //设置字体颜色,字体大小---   默认:文字居中
    private static XSSFCellStyle setCellStyle(int isMoreViewHead, int isNull, boolean remind) {//多视图,可为空,提示信息
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont font = workbook.createFont();
        if (0 == isNull) {// 属性必填  ---    0:必填   1:非必填
            font.setColor(HSSFColor.RED.index);
        }
        if (remind) {//是否是提示信息,位于表头具体字段的下一行
            font.setColor(HSSFColor.GREY_50_PERCENT.index);//提示信息搞成灰色
            font.setFontHeightInPoints((short) 10);
        } else {
            font.setFontHeightInPoints((short) 12);
            font.setBold(true);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        }
        if (isMoreViewHead > 0) { // 模型是多视图,并且当前是多视图的表头第一行---用来展示多视图的名称(合并单元格)      ---  0:否  1:是
            cellStyle.setFillForegroundColor(COLOR[isMoreViewHead - 1]);
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            font.setColor(HSSFColor.RED.index);
            font.setFontHeightInPoints((short) 16);
        }
        font.setFontName("宋体");
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置响应头
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            if (null != fileName) {
                fileName = fileName + ".xlsx";
            }
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
            response.setContentType("application/msdownload");// 定义输出类型
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
